SQL TOP, LIMIT or ROWNUM Clause

    To display or fetch the top N rows from the table, we would use the SQL TOP or LIMIT or ROWNUM clause. There are various RDBMS which uses the SQL for creating and modifying Relational Database , and all the RDBMS does not use give support for TOP command, for example, MySQL support LIMIT and Oracle support ROWNUM to fetch the top N records, but overall all these commands are used to perform the same operations.

    TOP syntax

    If you are using SQL Server / MS Access To use the TOP command follow this syntax: 

    SELECT TOP number|percent column_name(s)
FROM table_name;

    With Condition 

    SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

    LIMIT syntax

    if you are using MySQL 

    SELECT column_name,.....
FROM table_name
LIMIT number;

    ROWNUM Syntax

    if you are using Oracle 

    SELECT column_name,.....
FROM table_name
WHERE ROWNUM <= number;

    Example For the queries consider this table of students: 

    +------+--------+------+--------+-------+
| id   | name   | age  | grades | marks |
+------+--------+------+--------+-------+
|    1 | Luffy  |   16 | A      |   970 |
|    2 | Naruto |   18 | A      |   960 |
|    3 | Zoro   |   20 | A      |   940 |
|    4 | Sanji  |   21 | B      |   899 |
|    5 | Nami   |   17 | B      |   896 |
|    6 | Robin  | NULL | B      |   860 |
+------+--------+------+--------+-------+

    Query( SQL Server / MS Access ): Display the top 3 rows from the table students 

    SELECT TOP 3 * FROM students;
    Output 
    +------+--------+------+--------+-------+
| id   | name   | age  | grades | marks |
+------+--------+------+--------+-------+
|    1 | Luffy  |   16 | A      |   970 |
|    2 | Naruto |   18 | A      |   960 |
|    3 | Zoro   |   20 | A      |   940 |
+------+--------+------+--------+-------+

    Query( MySQL ): Display the top 4 rows from the table students 

    SELECT *
FROM students
LIMIT 4;

    Output 

    +------+--------+------+--------+-------+
| id   | name   | age  | grades | marks |
+------+--------+------+--------+-------+
|    1 | Luffy  |   16 | A      |   970 |
|    2 | Naruto |   18 | A      |   960 |
|    3 | Zoro   |   20 | A      |   940 |
|    4 | Sanji  |   21 | B      |   899 |
+------+--------+------+--------+-------+

    Query (Oracle): Display the top 4 rows from the table students: 

    SELECT * FROM students
WHERE ROWNUM <= 3;

    Output 

    +------+--------+------+--------+-------+
| id   | name   | age  | grades | marks |
+------+--------+------+--------+-------+
|    1 | Luffy  |   16 | A      |   970 |
|    2 | Naruto |   18 | A      |   960 |
|    3 | Zoro   |   20 | A      |   940 |
|    4 | Sanji  |   21 | B      |   899 |
+------+--------+------+--------+-------+

    Summary

    • To fetch the top N rows from a table we can SQL TOP, or LIMIT or ROWNUM clause.
    • Whether to TOP, LIMIT or ROWNUM depends on which RDBMS you are using.
    • MS Access usesTOP.
    • MySQL uses LIMIT.
    • Oracle uses ROWNUM.
    • We can also use the WHERE clause if we want to fetch the top rows from a specific data set.

    People are also reading:

    Previous Tutorial Next Tutorial